package com.controller;

import com.domain.History;
import com.domain.User;
import com.service.HistoryService;
import com.service.UserService;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

/*
    2021/9/5 21:00
    Project Name:drivingManagementSystem

    theme:
*/
@WebServlet("/xlsServlet")
public class XlsServlet extends HttpServlet {
    HistoryService service = new HistoryService();
    private List<History> alls;

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        try {
            String bool = req.getParameter("bool");//bool==1:本次考试导出，否则所有
            if(bool.equals("1"))//判断是 所有考试记录 还是 本次考试记录 的导出
                alls=service.findThisTime();
            else
                alls=service.findAll();

            downLoadXlsOfDevice(resp);
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    public void downLoadXlsOfDevice(HttpServletResponse response) throws IOException, WriteException {
        //创建一个输出流，用于将数据返回给浏览器
        ServletOutputStream outputStream = response.getOutputStream();
        //创建了一个全新的工作簿
        WritableWorkbook workbook = Workbook.createWorkbook(outputStream);
        //创建了一个工作表
        WritableSheet sheet = workbook.createSheet("历史记录", 0);
        //调整单元格列宽
        sheet.setColumnView(0,15);//第一个参数：列的索引值  第二个参数：标准字母的宽度
        sheet.setColumnView(1,15);
        sheet.setColumnView(2,30);
        sheet.setColumnView(3,15);
        sheet.setColumnView(4,10);
        //处理标题
        //列索引，行索引，单元格中的内容
        String[] titles=new String[]{"考试编号","学员学号","姓名","科目","得分"};
        Label label=null;
        //第一行插入属性列名称
        for (int i = 0; i < titles.length; i++) {
            label=new Label(i,0,titles[i]);
            sheet.addCell(label);
        }

        int rowIndex=1;
        for (History i : alls) {

            label = new Label(0, rowIndex, i.getId());
            sheet.addCell(label);

            label = new Label(1, rowIndex, i.getStu_id());
            sheet.addCell(label);

            label = new Label(2, rowIndex, i.getName());
            sheet.addCell(label);

            label = new Label(3, rowIndex, i.getSubject());
            sheet.addCell(label);

            label = new Label(4, rowIndex, i.getScore());
            sheet.addCell(label);

            rowIndex++;
        }
        //文件的导出 一个流两个头(文件的打开方式 in-line attachment附件，文件下载时的mime类型)
        String filename="历史记录.xls";
        response.setHeader("content-disposition","attachment;filename="+new String(filename.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.ms-excel");
        workbook.write();
        workbook.close();
        outputStream.close();
    }
}
